﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ADOX;
using ADODB;
using System.Collections;

namespace AccessMerge
{
    public partial class mainForm : Form
    {
        private int fileCount=0;
        private int recordCount=0;
        private ArrayList alField;
        public mainForm()
        {
            InitializeComponent();
            tbDirectory.Text=System.IO.Directory.GetCurrentDirectory()+"\\data";
        }

        private void btDirectory_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择文件路径";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                tbDirectory.Text = dialog.SelectedPath; 
            }

        }
        //读取文件
        private void btRead_Click(object sender, EventArgs e)
        {

            try
            {
                string path = tbDirectory.Text;
                string[] files = Directory.GetFiles(path, "*.mdb");
                fileCount = 0;
                foreach (string file in files)
                {
                    FileInfo info = new FileInfo(file);
                    double fileSize = Math.Ceiling(info.Length * 1.0 / 1024);
                    string fileName = info.Name;
                    dgvFile.Rows.Add(fileName, fileSize + "KB");
                    fileCount++;
                }
                tsbStatus.Text = "在" + path + "文件夹中共找到" + fileCount + "个MDB文件。";
                ShowIndex(dgvFile);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(),"错误");
                
            }
        }
        //显示行标
        public void ShowIndex(DataGridView dgv)
        {
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                dgv.Rows[i].HeaderCell.Value = (i + 1).ToString();  //HeaderCell获取或设置行的标头单元格             
            }
        }
        //合并记录
        private void btMerge_Click(object sender, EventArgs e)
        {
            try
            {
                //读取第一个表的内容
                recordCount = 0;
                dgvData.Rows.Clear();
                for (int i = 0; i < dgvFile.Rows.Count; i++)
                {
                    string filename = tbDirectory.Text + "\\" + dgvFile.Rows[i].Cells["filename"].Value.ToString();
                    string tablename = tbTableName.Text;
                    int count = fillGridWithData(filename, dgvData, tablename);
                    recordCount += count;
                    dgvFile.Rows[i].Cells["result"].Value = "共找到" + count + "条记录";

                }
                tsbStatus.Text = "在数据库文件中共获取" + recordCount + "条记录";
                ShowIndex(dgvData);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), "错误");
            }
        }
        /// <summary>
        /// 将指定路径的数据库内容读取到dgv里面
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="dgv"></param>
        public int fillGridWithData(string filename,DataGridView dgv,string tablename){
            int count = 0;
            OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename);
            thisConnection.Open();
            OleDbCommand thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "SELECT * FROM "+tablename;
            OleDbDataReader thisReader = thisCommand.ExecuteReader();
            while (thisReader.Read())
            {
                ArrayList al = new ArrayList();
                for (int i = 0; i < alField.Count;i++ )
                {
                    al.Add(thisReader[alField[i].ToString()].ToString());
                }
                string[] arrString = (string[])al.ToArray(typeof(string));
                dgv.Rows.Add(arrString);
                count++;
            }
            thisReader.Close();
            thisConnection.Close();
            return count;
        }
        //保存按钮    
        private void btSave_Click(object sender, EventArgs e)
        {
            try
            {
                //保存对话框
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "Access files(*.mdb)|*.mdb";
                saveFileDialog.FilterIndex = 0;
                //
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.Title = "保存数据到";
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    string filename = saveFileDialog.FileName.Trim();
                    string tablename = tbTableName.Text;
                    if (filename == "")
                    {
                        MessageBox.Show("请输入要保存的文件名", "提示");
                        return;
                    }
                    else if (File.Exists(filename))
                    {
                        MessageBox.Show("文件已经存在！");
                        return;
                    }
                    else
                    {
                        createDB(filename, tablename);
                        saveDB(filename, dgvData, tablename);
                        MessageBox.Show("导出完成！" + filename, "提示");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), "错误");
            }
        }
        //保存到datagridView数据到文件
        public void saveDB(string filename,DataGridView dgv,string tablename)
        {
            OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename);
            thisConnection.Open();
            OleDbCommand thisCommand = thisConnection.CreateCommand();
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                string sql = "insert into "+tablename+"(";
                for (int j = 0; j < alField.Count; j++)
                {
                    sql += alField[j].ToString() + ',';
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ") values ('";
                for (int j = 0; j < alField.Count; j++)
                {
                    string values = dgv.Rows[i].Cells[j].Value.ToString();
                    sql += values + "','";
                }
                sql = sql.Substring(0, sql.Length - 2);
                sql += ")";


                thisCommand.CommandText = sql;
                thisCommand.ExecuteNonQuery();

            }
            thisConnection.Close();
        }
        //创建数据库文件
        public void createDB(string filename,string tablename){
            ADOX.Catalog catalog = new Catalog();
            catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=" + filename);
            ADODB.Connection cn = new ADODB.Connection();

            cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename, null, null, -1);
            catalog.ActiveConnection = cn;

            ADOX.Table table = new ADOX.Table();
            table.Name = tablename;
            for (int i = 0; i < alField.Count;i++ )
            {
                table.Columns.Append(alField[i].ToString(), ADOX.DataTypeEnum.adVarWChar, 255);
            }
            catalog.Tables.Append(table);
            cn.Close();
        }
        //获取字段结构
        private void btGetField_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvFile.Rows.Count <= 0)
                {
                    MessageBox.Show("没有数据文件,请指定正确的路径，并读取文件列表", "提示");
                    return;
                }

                string filename = tbDirectory.Text + "\\" + dgvFile.Rows[0].Cells["filename"].Value.ToString();
                string tablename = tbTableName.Text;
                alField = getDBFields(filename, tablename);
                buildDataGridView(dgvData, alField);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), "错误");
            }
            
        }
        /// <summary>
        /// 获取指定access文件的指定表名字段信息
        /// </summary>
        /// <param name="filename">数据库文件名</param>
        /// <param name="tablename">表名</param>
        /// <returns>字段名列表</returns>
        public ArrayList getDBFields(string filename,string tablename)
        {
            ArrayList arrayList = new ArrayList();
            DataTable dt = new DataTable();
            OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename);
            thisConnection.Open();
            OleDbCommand thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "SELECT * FROM "+tablename;
            OleDbDataReader thisReader = thisCommand.ExecuteReader();
            int fieldcount = thisReader.FieldCount;
            dt = thisReader.GetSchemaTable();
            for (int i = 0; i < fieldcount; i++)
            {
                arrayList.Add(thisReader.GetName(i));
            }
            return arrayList;
        }

        /// <summary>
        /// 填充datagridView结构
        /// </summary>
        /// <param name="dgv">datagridView</param>
        /// <param name="al">字段列表</param>
        public void buildDataGridView(DataGridView dgv,ArrayList al)
        {
            dgv.Columns.Clear();
            for (int i = 0; i < al.Count; i++)
            {
                DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
                column.Name = "field"+i;
                column.HeaderText = al[i].ToString();
                column.ReadOnly = true;
                dgv.Columns.Add(column);
            }
            dgv.AllowUserToAddRows = false;
            dgv.AllowUserToDeleteRows = false;
        }
 
    }
}
